﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;

namespace Bepo.Model.Helpers
{
    [Serializable]
    public class DatabaseDirect
    {
        public string ServerName { get; set; }
        public string DatabaseName { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }

        public string ToConnectionString()
        {
            return ToConnectionString(600);
        }

        public string ToConnectionString(int? timeout)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = this.ServerName;
            if (this.DatabaseName != null) builder.InitialCatalog = this.DatabaseName;
            if (!string.IsNullOrEmpty(this.UserName))
            {
                builder.UserID = this.UserName;
                builder.Password = this.Password;
            }
            else builder.IntegratedSecurity = true;

            builder.ConnectTimeout = timeout.HasValue ? timeout.Value : 600;
            builder.MinPoolSize = 10;
            return builder.ConnectionString;
        }

        public override string ToString()
        {
            return ToConnectionString();
        }

        public IDbConnection GetConnection()
        { 
        return new SqlConnection(this.ToConnectionString());
        }

        public bool Test()
        {
            bool isConnectionValid = false;

            SqlConnection connection = new SqlConnection(this.ToConnectionString(10));
            try
            {
                connection.Open();
                isConnectionValid = (connection.State == System.Data.ConnectionState.Open);
            }
            catch (SqlException x)
            {
                // Unable to connect
                System.Diagnostics.Trace.WriteLine(x.Message);
            }
            finally
            {
                connection.Close();
            }

            return isConnectionValid;
        }

        public int ExecuteScript(string script)
        {
            if (string.IsNullOrEmpty(script))
                return 0;

            var scripts = SplitSqlScript(script);

            if (scripts == null || scripts.Count() == 0)
                return 0;

            var affected = 0;

            using (SqlConnection conn = new SqlConnection(this.ToConnectionString()))
            {
                conn.Open();

                scripts.ToList().ForEach(x =>
                {
                    using (SqlCommand cmd = new SqlCommand(x, conn))
                    {
                        affected += cmd.ExecuteNonQuery();
                    }
                });

                conn.Close();
            }

            return affected;
        }

        public object ExecuteScalar(string commandText)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(this.ToConnectionString()))
            {
                conn.Open();

                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {
                    result = cmd.ExecuteScalar();
                }

                conn.Close();
            }

            return (result == DBNull.Value) ? null: result;
        }

        public DataSet ExecuteDataSet(string script)
        {
            if (string.IsNullOrEmpty(script))
                return null;

            var scripts = SplitSqlScript(script);

            if (scripts == null || scripts.Count() == 0)
                return null;

            DataSet result = null;
            int i = 0;
            using (SqlConnection conn = new SqlConnection(this.ToConnectionString()))
            {
                conn.Open();
                scripts.ToList().ForEach(x =>
                {
                    if (result == null) result = new DataSet();
                    var dt = new DataTable(i.ToString());
                    using (SqlDataAdapter adp = new SqlDataAdapter(x, conn))
                    {
                        adp.Fill(dt);
                    }
                    result.Tables.Add(dt);
                    i++;
                });

                conn.Close();
            }

            return result;
        }

        public static DatabaseDirect Get(string connectionString)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
            DatabaseDirect infos = new DatabaseDirect();
            infos.ServerName = builder.DataSource;
            infos.DatabaseName = builder.InitialCatalog;
            if (!builder.IntegratedSecurity)
            {
                infos.UserName = builder.UserID;
                infos.Password = builder.Password;
            }
            return infos;
        }

        public static DatabaseDirect GetFromConfigFile(string connectionStringName)
        {
            return Get(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);
        }

        public static DatabaseDirect Get(string ServerName, string DatabaseName)
        {
            return Get(ServerName, DatabaseName, null, null);
        }

        public static DatabaseDirect Get(string ServerName, string DatabaseName, string UserName, string Password)
        {
            DatabaseDirect infos = new DatabaseDirect();
            infos.ServerName = ServerName;
            infos.DatabaseName = DatabaseName;
            infos.UserName = UserName;
            infos.Password = Password;
            return infos;
        }

        static IEnumerable<string> SplitSqlScript(string script)
        {
            Regex splitter = new Regex(@"\n\s*(GO|go|Go|gO)\s*\n?");
            string[] scripts = splitter.Split(script);

            if (scripts == null || scripts.Length == 0 || (scripts.Length == 1 && string.IsNullOrWhiteSpace(scripts[0])))
                return null;

            var res = scripts.Where(s => !string.IsNullOrWhiteSpace(s) && s.Trim().ToLowerInvariant() != "go");
            return res;
        }
                
        string MachineName
        {
            get
            {
                var splitted = ServerName.Split('\\');
                if (splitted.Length > 1)
                {
                    return splitted[0];
                }
                return ServerName;
            }
        }

        string InstanceName
        {
            get
            {
                var splitted = ServerName.Split('\\');
                if (splitted.Length > 1)
                {
                    return splitted[1];
                }
                return ServerName;
            }
        }
    }
}
